const Database = require('better-sqlite3');

const db = new Database('./data/delivery-management.db');

try {
  console.log('=== 验证实例表增强结果 ===');
  
  // 1. 检查字段说明表
  console.log('1. 字段说明表统计:');
  
  const mappingStats = db.prepare(`
    SELECT 
      source_table_type,
      COUNT(*) as field_count,
      COUNT(DISTINCT instance_table_name) as table_count
    FROM instance_field_mappings
    GROUP BY source_table_type
    ORDER BY field_count DESC
  `).all();
  
  console.log('字段来源统计:');
  mappingStats.forEach(stat => {
    console.log(`- ${stat.source_table_type}: ${stat.field_count} 个字段，分布在 ${stat.table_count} 个实例表中`);
  });
  
  const totalMappings = db.prepare('SELECT COUNT(*) as count FROM instance_field_mappings').get();
  console.log(`\\n总字段映射数: ${totalMappings.count}`);
  
  // 2. 验证冷却水喷淋装置实例表
  console.log('\\n2. 冷却水喷淋装置实例表验证:');
  
  const coolingTableName = 'device_instances_piping_cooling_water_spray_system';
  const coolingFields = db.prepare(`PRAGMA table_info([${coolingTableName}])`).all();
  
  console.log(`表名: ${coolingTableName}`);
  console.log(`总字段数: ${coolingFields.length}`);
  
  // 分类统计字段
  const baseFields = coolingFields.filter(f => 
    ['id', 'device_type_code', 'instance_number', 'object_number', 'object_description', 'created_at', 'updated_at'].includes(f.name)
  );
  
  const namingFields = coolingFields.filter(f => f.name.startsWith('naming_'));
  const positionFields = coolingFields.filter(f => f.name.startsWith('position_'));
  const baseParamsFields = coolingFields.filter(f => f.name.startsWith('base_params_'));
  
  console.log(`- 基础字段: ${baseFields.length} 个`);
  console.log(`- 命名属性字段: ${namingFields.length} 个`);
  console.log(`- 位置属性字段: ${positionFields.length} 个`);
  console.log(`- 基础参数字段: ${baseParamsFields.length} 个`);
  
  // 3. 显示字段示例
  console.log('\\n3. 字段示例:');
  
  if (namingFields.length > 0) {
    console.log('命名属性字段示例:');
    namingFields.slice(0, 3).forEach(field => {
      console.log(`  - ${field.name}`);
    });
  }
  
  if (positionFields.length > 0) {
    console.log('位置属性字段示例:');
    positionFields.slice(0, 3).forEach(field => {
      console.log(`  - ${field.name}`);
    });
  }
  
  if (baseParamsFields.length > 0) {
    console.log('基础参数字段示例:');
    baseParamsFields.slice(0, 3).forEach(field => {
      console.log(`  - ${field.name}`);
    });
  }
  
  // 4. 查看该表的字段映射详情
  console.log('\\n4. 字段映射详情:');
  
  const coolingMappings = db.prepare(`
    SELECT field_name, source_table, source_table_type, description
    FROM instance_field_mappings 
    WHERE instance_table_name = ?
    ORDER BY source_table_type, field_name
    LIMIT 5
  `).all(coolingTableName);
  
  console.log('字段映射示例:');
  coolingMappings.forEach(mapping => {
    console.log(`- ${mapping.field_name}: 来自${mapping.source_table_type}表(${mapping.source_table})`);
  });
  
  // 5. 验证不同专业的实例表
  console.log('\\n5. 不同专业实例表对比:');
  
  const exampleTables = [
    'device_instances_piping_cooling_water_spray_system',
    'device_instances_instrumentation_pressure_transmitter', 
    'device_instances_electrical_power_transformer'
  ];
  
  for (const tableName of exampleTables) {
    const tableExists = db.prepare(`
      SELECT name FROM sqlite_master 
      WHERE type='table' AND name=?
    `).get(tableName);
    
    if (tableExists) {
      const fields = db.prepare(`PRAGMA table_info([${tableName}])`).all();
      const attrFields = fields.filter(f => 
        f.name.startsWith('naming_') || f.name.startsWith('position_') || f.name.startsWith('base_params_')
      );
      
      console.log(`- ${tableName}: ${attrFields.length} 个属性字段`);
    } else {
      console.log(`- ${tableName}: 表不存在`);
    }
  }
  
  // 6. 检查字段命名规律
  console.log('\\n6. 字段命名规律验证:');
  
  const fieldPatterns = db.prepare(`
    SELECT 
      CASE 
        WHEN field_name LIKE 'naming_%' THEN 'naming'
        WHEN field_name LIKE 'position_%' THEN 'position'  
        WHEN field_name LIKE 'base_params_%' THEN 'base_params'
        ELSE 'other'
      END as field_prefix,
      COUNT(*) as count
    FROM instance_field_mappings
    GROUP BY field_prefix
    ORDER BY count DESC
  `).all();
  
  console.log('字段命名前缀统计:');
  fieldPatterns.forEach(pattern => {
    console.log(`- ${pattern.field_prefix}_*: ${pattern.count} 个字段`);
  });
  
  // 7. 总结增强效果
  console.log('\\n=== 增强效果总结 ===');
  
  const totalTables = db.prepare(`
    SELECT COUNT(DISTINCT instance_table_name) as count
    FROM instance_field_mappings
  `).get();
  
  const avgFieldsPerTable = db.prepare(`
    SELECT 
      AVG(field_count) as avg_fields,
      MIN(field_count) as min_fields,
      MAX(field_count) as max_fields
    FROM (
      SELECT COUNT(*) as field_count
      FROM instance_field_mappings
      GROUP BY instance_table_name
    )
  `).get();
  
  console.log('✅ 增强完成统计:');
  console.log(`- 增强的实例表数量: ${totalTables.count}`);
  console.log(`- 平均每表字段数: ${Math.round(avgFieldsPerTable.avg_fields)}`);
  console.log(`- 最少字段数: ${avgFieldsPerTable.min_fields}`);
  console.log(`- 最多字段数: ${avgFieldsPerTable.max_fields}`);
  console.log(`- 总字段映射数: ${totalMappings.count}`);
  
  console.log('\\n✅ 实例表结构增强成功！');
  console.log('- 所有实例表都包含了对应对象类的三个属性表字段');
  console.log('- 字段说明表记录了每个字段的来源信息');
  console.log('- 为属性数据提供了统一的存储位置');
  console.log('- 数据结构更加高效和易于查询');

} catch (error) {
  console.error('验证失败:', error);
} finally {
  db.close();
}